package fina2.dcs.common.xls;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.jboss.seam.log.Log;
import org.jboss.seam.log.Logging;

import fina2.dcs.common.security.UserType;

public class XLSValidator {

	private static Log log = Logging.getLog(XLSValidator.class);

	private static ArrayList<String> unprotectedSheets = new ArrayList<String>();

	private static ArrayList<String> nopasswordSheets = new ArrayList<String>();
	private static List<MatrixOptionsRowInfo> rowInfo = new ArrayList<MatrixOptionsRowInfo>();
	private static List<String> unconfirmedSheets = new ArrayList<String>();

	public static ArrayList<String> getUnprotectedSheets() {
		return unprotectedSheets;
	}

	public static ArrayList<String> getNopasswordSheets() {
		return nopasswordSheets;
	}

	public static List<MatrixOptionsRowInfo> getRowInfo() {
		return rowInfo;
	}

	public static List<String> getUnconfirmedSheets() {
		return unconfirmedSheets;
	}

	private XLSValidator() {

	}

	@SuppressWarnings("unused")
	/**
	 * please use isXlsFile(byte[] fileContent)
	 */
	@Deprecated
	public static boolean isXLSFile(File file) {

		boolean isXLS = true;
		try {
			FileInputStream fis = new FileInputStream(file);
			POIFSFileSystem s = new POIFSFileSystem(fis);
			HSSFWorkbook wb = new HSSFWorkbook(s);
		} catch (OfficeXmlFileException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (IOException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (IllegalArgumentException ex) {
			isXLS = false;
			log.error(ex.getMessage());
		} catch (Exception ex) {
			isXLS = false;
			log.error(ex.getMessage(), ex);
		}
		return isXLS;
	}

	@SuppressWarnings("unused")
	/**
	 * Checkd File Validity by content
	 */
	public static boolean isXLSFile(byte[] fileContent) {

		boolean isXLS = true;
		try {
			ByteArrayInputStream fis = new ByteArrayInputStream(fileContent);
			POIFSFileSystem s = new POIFSFileSystem(fis);
			HSSFWorkbook wb = new HSSFWorkbook(s);
		} catch (OfficeXmlFileException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (IOException ex) {
			isXLS = false;
			log.info(ex.getMessage());
		} catch (IllegalArgumentException ex) {
			isXLS = false;
			log.error(ex.getMessage());
		} catch (Exception ex) {
			isXLS = false;
			log.error(ex.getMessage(), ex);
		}
		return isXLS;
	}

	public static String getMatrixByNam1e(String xlsName, String spDefault, String spMfbHis, Properties p) {
		String mainMat = "C:\\fina-server\\Matrix.xls";
		String matrix = "C:\\fina-server\\";
		try {

			XLSReader xlsReader = new XLSReader(new File(mainMat), p);
			HSSFWorkbook matWb = new HSSFWorkbook(new FileInputStream(new File(mainMat)));
			if (isXLSNameValidBy(xlsName, spDefault.toLowerCase())) {
				matrix += xlsReader.getValueByRowCol(matWb, "Options", 0, 1).toString().trim();
			} else if (isXLSNameValidBy(xlsName, spMfbHis.toLowerCase())) {
				matrix += xlsReader.getValueByRowCol(matWb, "Options", 1, 1).toString().trim();
			}

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return matrix;
	}

	/**
	 * @deprecated
	 * @param xlsFile
	 * @param spDefault
	 * @param spMfbHis
	 * @param p
	 * @return
	 * @throws MatrixException
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String validateMatrix(File xlsFile, String spDefault, String spMfbHis, Properties p) throws MatrixException, FileNotFoundException, IOException {
		File mainMat = new File("C:\\fina-server\\Matrix.xls");
		String matLoc = "C:\\fina-server\\";
		String xlsName = xlsFile.getName();
		String messageCode = null;
		if (!mainMat.exists()) {
			messageCode = MatrixExceptionConstants.MAIN_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}
		XLSReader xlsReader = new XLSReader(mainMat, p);

		HSSFWorkbook mainMatWb = new HSSFWorkbook(new FileInputStream(mainMat));

		if (isXLSNameValidBy(xlsName, spDefault.toLowerCase())) {
			Object s = xlsReader.getValueByRowCol(mainMatWb, "Options", 0, 1);
			if ((s == null) || (s.toString().trim().equals(""))) {
				messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX.getMessageCode();
				throw new MatrixException(messageCode);
			}
			matLoc += s.toString().trim();
		} else if (isXLSNameValidBy(xlsName, spMfbHis.toLowerCase())) {
			Object s = xlsReader.getValueByRowCol(mainMatWb, "Options", 1, 1);
			if ((s == null) || (s.toString().trim().equals(""))) {
				messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX.getMessageCode();
				throw new MatrixException(messageCode);
			}
			matLoc += s.toString().trim();
		} else {
			messageCode = MatrixExceptionConstants.ILLEGAL_FILENAME.getMessageCode();
			throw new MatrixException(messageCode);
		}

		File mat = new File(matLoc);
		if (mat.isDirectory()) {

		}
		if (!mat.exists()) {
			messageCode = MatrixExceptionConstants.CHANGED_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}

		HSSFWorkbook matWb = new HSSFWorkbook(new FileInputStream(mat));
		if (!xlsReader.containsSheet(matWb, "Options")) {
			messageCode = MatrixExceptionConstants.OPTIONS_SHEET_ERROR.getMessageCode();
			throw new MatrixException(messageCode);
		}

		int rowCounter = 1;

		while (xlsReader.getValueByRowCol(matWb, "Options", rowCounter, 0) != null) {
			rowCounter++;
		}

		for (int i = 1; i < rowCounter; i++) {
			Object columnBVal = xlsReader.getValueByRowCol(matWb, "Options", i, 1);
			Object columnDVal = xlsReader.getValueByRowCol(matWb, "Options", i, 3);
			Object columnHVal = xlsReader.getValueByRowCol(matWb, "Options", i, 7);
			Object columnMVal = xlsReader.getValueByRowCol(matWb, "Options", i, 12);
			if (columnBVal == null || columnDVal == null || columnHVal == null || columnMVal == null) {
				messageCode = MatrixExceptionConstants.COLUMN_FILL_ERROR.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}
		HSSFWorkbook xlsWb = new HSSFWorkbook(new FileInputStream(xlsFile));
		for (int i = 1; i < rowCounter; i++) {
			String sheetName = xlsReader.getValueByRowCol(matWb, "Options", i, 12).toString().trim();
			if (!xlsReader.containsSheet(matWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
			if (!xlsReader.containsSheet(xlsWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}

		return messageCode;
	}

	public static List<String> loadValidPatterns(UserType userType, List<String> userFiTypes, Properties p, String matLoc) throws FileNotFoundException, IOException {
		File mainMat = new File(matLoc + System.getProperty("file.separator") + MatrixUtil.DEFAULT_MAIN_MATRIX_NAME);
		XLSReader xlsReader = new XLSReader(mainMat, p);
		List<String> patternsList = new ArrayList<String>();
		HSSFWorkbook mainMatWb = new HSSFWorkbook(new FileInputStream(mainMat));

		List<MatrixOptionsRowInfo> rows = loadRowInfo(userType, xlsReader, mainMatWb, userFiTypes);
		for (MatrixOptionsRowInfo info : rows) {
			patternsList.add(info.getPattern());
		}
		return patternsList;
	}

	public static XlsFileProperties loadXlsFileProps(String xlsFileName, UserType userType, List<String> userFiTypes, XlsFileProperties xlsFileProps, Properties p, String matrixPath) throws MatrixException, FileNotFoundException, IOException {
		if (xlsFileProps == null)
			xlsFileProps = new XlsFileProperties();

		String matLoc = matrixPath + System.getProperty("file.separator");
		File mainMat = new File(matLoc + MatrixUtil.DEFAULT_MAIN_MATRIX_NAME);

		// String xlsName = xlsFileName;
		String messageCode = null;
		if (!mainMat.exists()) {
			messageCode = MatrixExceptionConstants.MAIN_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}
		XLSReader xlsReader = new XLSReader(mainMat, p);

		HSSFWorkbook mainMatWb = new HSSFWorkbook(new FileInputStream(mainMat));

		List<MatrixOptionsRowInfo> rows = loadRowInfo(userType, xlsReader, mainMatWb, userFiTypes);
		for (MatrixOptionsRowInfo info : rows) {
			if (isXLSNameValidBy(xlsFileName, info.getPattern())) {
				Object s = info.getTypeMatrix();
				if ((s == null) || (s.toString().trim().equals(""))) {
					messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX.getMessageCode();
					throw new MatrixException(messageCode);
				}
				matLoc += s.toString().trim();
				xlsFileProps.setXlsNameValid(true);
				xlsFileProps.setXlsCorrectPattern(info.getPattern());
				break;
			}
		}
		return xlsFileProps;
	}

	/*
	 * Please use
	 */
	@Deprecated
	public static String validateMatrix(File xlsFile, UserType userType, List<String> userFiTypes, XlsFileProperties xlsFileProps, Properties p, String mainMatrixPath) throws MatrixException, FileNotFoundException, IOException {
		if (xlsFileProps == null)
			xlsFileProps = new XlsFileProperties();
		String matLoc = mainMatrixPath + System.getProperty("file.separator");
		File mainMat = new File(matLoc + MatrixUtil.DEFAULT_MAIN_MATRIX_NAME);
		String xlsName = xlsFile.getName();
		String messageCode = null;
		if (!mainMat.exists()) {
			messageCode = MatrixExceptionConstants.MAIN_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}
		XLSReader xlsReader = new XLSReader(mainMat, p);

		HSSFWorkbook mainMatWb = new HSSFWorkbook(new FileInputStream(mainMat));

		List<MatrixOptionsRowInfo> rows = loadRowInfo(userType, xlsReader, mainMatWb, userFiTypes);
		for (MatrixOptionsRowInfo info : rows) {
			if (isXLSNameValidBy(xlsName, info.getPattern())) {
				Object s = info.getTypeMatrix();
				if ((s == null) || (s.toString().trim().equals(""))) {
					messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX.getMessageCode();
					throw new MatrixException(messageCode);
				}
				matLoc += s.toString().trim();
				xlsFileProps.setXlsNameValid(true);
				xlsFileProps.setXlsCorrectPattern(info.getPattern());
				break;
			}
		}

		xlsFileProps.setMatrixLoc(matLoc);

		File mat = new File(matLoc);
		if (mat.isDirectory()) {
			messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX_FOR_NON_VALID_FILE.getMessageCode();
			throw new MatrixException(messageCode);
		}
		if (!mat.exists()) {
			messageCode = MatrixExceptionConstants.CHANGED_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}

		HSSFWorkbook matWb = new HSSFWorkbook(new FileInputStream(mat));
		if (!xlsReader.containsSheet(matWb, "Options")) {
			messageCode = MatrixExceptionConstants.OPTIONS_SHEET_ERROR.getMessageCode();
			throw new MatrixException(messageCode);
		}

		int rowCounter = 1;

		while (xlsReader.getValueByRowCol(matWb, "Options", rowCounter, 0) != null) {
			rowCounter++;
		}

		for (int i = 1; i < rowCounter; i++) {
			Object columnBVal = xlsReader.getValueByRowCol(matWb, "Options", i, 1);
			Object columnDVal = xlsReader.getValueByRowCol(matWb, "Options", i, 3);
			Object columnHVal = xlsReader.getValueByRowCol(matWb, "Options", i, 7);
			Object columnMVal = xlsReader.getValueByRowCol(matWb, "Options", i, 12);
			if (columnBVal == null || columnDVal == null || columnHVal == null || columnMVal == null) {
				messageCode = MatrixExceptionConstants.COLUMN_FILL_ERROR.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}
		HSSFWorkbook xlsWb = new HSSFWorkbook(new FileInputStream(xlsFile));
		for (int i = 1; i < rowCounter; i++) {
			String sheetName = xlsReader.getValueByRowCol(matWb, "Options", i, 12).toString().trim();
			if (!xlsReader.containsSheet(matWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
			if (!xlsReader.containsSheet(xlsWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}

		XLSReader matXlsReader = new XLSReader(mat, p);
		XLSReader masterXlsReader = new XLSReader(xlsFile, p);

		Map<String, Short> matData = matXlsReader.getAllSheetData();
		Map<String, Short> xlsData = masterXlsReader.getAllSheetData();
		Map<String, Boolean> matTrueFalseData = matXlsReader.getAllTrueFalseData();

		if (xlsData.size() > (matData.size() - 1)) {
			messageCode = MatrixExceptionConstants.XLS_FILE_SHEET_QUANTITY_ERROR.getMessageCode();
			throw new MatrixException(messageCode);
		}
		unconfirmedSheets = getAllUncomfirmedSheets(matData, xlsData, matTrueFalseData);
		if ((unconfirmedSheets != null) && (unconfirmedSheets.size() > 0)) {
			messageCode = MatrixExceptionConstants.MATRIX_SHEET_CONFIRMATION_ERROR.getMessageCode();
			xlsFileProps.setUnconfirmedSheets(unconfirmedSheets);
			throw new MatrixException(messageCode);
		}
		return messageCode;
	}

	public static String validateMatrix(String xlsFileName, byte[] fileContent, UserType userType, List<String> userFiTypes, XlsFileProperties xlsFileProps, Properties p, String mainMatrixPath) throws MatrixException, FileNotFoundException, IOException {
		if (xlsFileProps == null)
			xlsFileProps = new XlsFileProperties();
		String matLoc = mainMatrixPath + System.getProperty("file.separator");
		File mainMat = new File(matLoc + MatrixUtil.DEFAULT_MAIN_MATRIX_NAME);

		String messageCode = null;
		if (!mainMat.exists()) {
			messageCode = MatrixExceptionConstants.MAIN_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}
		XLSReader xlsReader = new XLSReader(mainMat, p);

		HSSFWorkbook mainMatWb = new HSSFWorkbook(new FileInputStream(mainMat));

		List<MatrixOptionsRowInfo> rows = loadRowInfo(userType, xlsReader, mainMatWb, userFiTypes);
		for (MatrixOptionsRowInfo info : rows) {
			if (isXLSNameValidBy(xlsFileName, info.getPattern())) {
				Object s = info.getTypeMatrix();
				if ((s == null) || (s.toString().trim().equals(""))) {
					messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX.getMessageCode();
					throw new MatrixException(messageCode);
				}
				matLoc += s.toString().trim();
				xlsFileProps.setXlsNameValid(true);
				xlsFileProps.setXlsCorrectPattern(info.getPattern());
				break;
			}
		}

		xlsFileProps.setMatrixLoc(matLoc);

		File mat = new File(matLoc);
		if (mat.isDirectory()) {
			messageCode = MatrixExceptionConstants.ERROR_CHANGE_MATRIX_FOR_NON_VALID_FILE.getMessageCode();
			throw new MatrixException(messageCode);
		}
		if (!mat.exists()) {
			messageCode = MatrixExceptionConstants.CHANGED_MATRIX_DOESNOT_EXIST.getMessageCode();
			throw new MatrixException(messageCode);
		}

		HSSFWorkbook matWb = new HSSFWorkbook(new FileInputStream(mat));
		if (!xlsReader.containsSheet(matWb, "Options")) {
			messageCode = MatrixExceptionConstants.OPTIONS_SHEET_ERROR.getMessageCode();
			throw new MatrixException(messageCode);
		}

		int rowCounter = 1;

		while (xlsReader.getValueByRowCol(matWb, "Options", rowCounter, 0) != null) {
			rowCounter++;
		}

		for (int i = 1; i < rowCounter; i++) {
			Object columnBVal = xlsReader.getValueByRowCol(matWb, "Options", i, 1);
			Object columnDVal = xlsReader.getValueByRowCol(matWb, "Options", i, 3);
			Object columnHVal = xlsReader.getValueByRowCol(matWb, "Options", i, 7);
			Object columnMVal = xlsReader.getValueByRowCol(matWb, "Options", i, 12);
			if (columnBVal == null || columnDVal == null || columnHVal == null || columnMVal == null) {
				messageCode = MatrixExceptionConstants.COLUMN_FILL_ERROR.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}
		HSSFWorkbook xlsWb = new HSSFWorkbook(new ByteArrayInputStream(fileContent));
		for (int i = 1; i < rowCounter; i++) {
			String sheetName = xlsReader.getValueByRowCol(matWb, "Options", i, 12).toString().trim();
			if (!xlsReader.containsSheet(matWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
			if (!xlsReader.containsSheet(xlsWb, sheetName)) {
				messageCode = MatrixExceptionConstants.SHEET_NOT_EXISTS.getMessageCode();
				throw new MatrixException(messageCode);
			}
		}

		XLSReader matXlsReader = new XLSReader(mat, p);
		XLSReader masterXlsReader = new XLSReader(fileContent, p);

		Map<String, Short> matData = matXlsReader.getAllSheetData();
		Map<String, Short> xlsData = masterXlsReader.getAllSheetData();
		Map<String, Boolean> matTrueFalseData = matXlsReader.getAllTrueFalseData();

		if (xlsData.size() > (matData.size() - 1)) {
			messageCode = MatrixExceptionConstants.XLS_FILE_SHEET_QUANTITY_ERROR.getMessageCode();
			throw new MatrixException(messageCode);
		}
		unconfirmedSheets = getAllUncomfirmedSheets(matData, xlsData, matTrueFalseData);
		if ((unconfirmedSheets != null) && (unconfirmedSheets.size() > 0)) {
			messageCode = MatrixExceptionConstants.MATRIX_SHEET_CONFIRMATION_ERROR.getMessageCode();
			xlsFileProps.setUnconfirmedSheets(unconfirmedSheets);
			throw new MatrixException(messageCode);
		}
		return messageCode;
	}

	private static List<String> getAllUncomfirmedSheets(Map<String, Short> matData, Map<String, Short> xlsData, Map<String, Boolean> trueFalseData) {
		List<String> list = new ArrayList<String>();
		Iterator<String> matDataKeyIter = matData.keySet().iterator();
		nopasswordSheets.clear();
		while (matDataKeyIter.hasNext()) {
			String matDataKey = matDataKeyIter.next();
			if (matDataKey.toLowerCase().trim().equals("options"))
				continue;

			short matDataValue = matData.get(matDataKey);
			Object val = xlsData.get(matDataKey);
			if (val == null)
				break;
			short xlsDataValue = xlsData.get(matDataKey);
			if ((matDataValue != xlsDataValue) && (trueFalseData.get(matDataKey))) {
				list.add(matDataKey);
				nopasswordSheets.add(matDataKey);
			}
		}
		return list;
	}

	private static List<MatrixOptionsRowInfo> loadRowInfo(UserType usrType, XLSReader xlsReader, HSSFWorkbook mainMatWb, List<String> userFiTypes) {
		//
		rowInfo.clear();
		Object code = null;
		int rowIndex = 1;
		if (usrType.equals(UserType.ADMINISTRATOR)) {
			do {
				code = xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 0);
				if ((code == null) || (code.toString().trim().equals("")))
					break;
				MatrixOptionsRowInfo info = new MatrixOptionsRowInfo();
				info.setFiTypeCode(code.toString());
				info.setTypeMatrix(xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 1).toString());
				info.setPattern(xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 2).toString());
				info.setPeriodTypeCode(xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 3).toString());
				info.setRetVersion(xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 5).toString());
				rowInfo.add(info);
				rowIndex++;
			} while (code != null);
		} else {
			for (String type : userFiTypes) {
				XlsPoint point = xlsReader.searchBy(type, 1, 0);
				if (point != null) {
					MatrixOptionsRowInfo info = new MatrixOptionsRowInfo();
					info.setFiTypeCode(type);
					info.setTypeMatrix(xlsReader.getValueByRowCol(mainMatWb, "Options", point.getRowIndex(), 1).toString());
					info.setPattern(xlsReader.getValueByRowCol(mainMatWb, "Options", point.getRowIndex(), 2).toString());
					info.setPeriodTypeCode(xlsReader.getValueByRowCol(mainMatWb, "Options", point.getRowIndex(), 3).toString());
					info.setRetVersion(xlsReader.getValueByRowCol(mainMatWb, "Options", rowIndex, 5).toString());
					rowInfo.add(info);
				}
			}
		}
		return rowInfo;
	}

	public static boolean isXLSNameValidBy(String name, String pattern) {
		Pattern p = Pattern.compile(pattern.toLowerCase().trim());
		Matcher m = p.matcher(name.toLowerCase().trim());
		return m.matches();
	}

	public static boolean isXLSNameValid(String name, String spDefault, String spHistoric) {
		String regEx = null;

		boolean isValid = false;

		try {

			regEx = spDefault.toLowerCase();
			Pattern pattern = Pattern.compile(regEx);
			Matcher matches = pattern.matcher(name.toLowerCase());
			if (matches.matches()) {
				isValid = true;
			} else {
				regEx = spHistoric.toLowerCase();
				pattern = Pattern.compile(regEx);
				matches = pattern.matcher(name.toLowerCase());
				if (matches.matches()) {
					isValid = true;
				}
			}
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return isValid;
	}
    /*
     * Please use  isXLSProtectionValidByOptions(byte[] xlsContent, String matrixPath, Properties p)
     * */
	@Deprecated
	public static boolean isXLSProtectionValidByOptions(File xls, String matrixPath, Properties p) {

		boolean isProtectedByOpt = true;
		try {
			File matrix = new File(matrixPath);

			XLSReader xlsReader = new XLSReader(matrix, p);
			TreeMap<String, Boolean> options = xlsReader.loadProtectionProperties();
			if (options.size() > 0) {
				FileInputStream fis = new FileInputStream(xls);
				POIFSFileSystem s = new POIFSFileSystem(fis);
				HSSFWorkbook workbook = new HSSFWorkbook(s);

				Iterator<String> iter = options.keySet().iterator();
				unprotectedSheets.clear();
				while (iter.hasNext()) {
					String key = iter.next();
					HSSFSheet sheet = workbook.getSheet(key);
					if (options.get(key) && (!sheet.getProtect())) {
						isProtectedByOpt = false;
						unprotectedSheets.add(sheet.getSheetName());
					}
				}
			} else {
				isProtectedByOpt = false;
			}

		} catch (Exception ex) {
			isProtectedByOpt = false;
			// ex.printStackTrace();
		}

		return isProtectedByOpt;
	}

	public static boolean isXLSProtectionValidByOptions(byte[] xlsContent, String matrixPath, Properties p) {

		boolean isProtectedByOpt = true;
		try {
			File matrix = new File(matrixPath);

			XLSReader xlsReader = new XLSReader(matrix, p);
			TreeMap<String, Boolean> options = xlsReader.loadProtectionProperties();
			if (options.size() > 0) {

				POIFSFileSystem s = new POIFSFileSystem(new ByteArrayInputStream(xlsContent));
				HSSFWorkbook workbook = new HSSFWorkbook(s);

				Iterator<String> iter = options.keySet().iterator();
				unprotectedSheets.clear();
				while (iter.hasNext()) {
					String key = iter.next();
					HSSFSheet sheet = workbook.getSheet(key);
					if (options.get(key) && (!sheet.getProtect())) {
						isProtectedByOpt = false;
						unprotectedSheets.add(sheet.getSheetName());
					}
				}
			} else {
				isProtectedByOpt = false;
			}

		} catch (Exception ex) {
			isProtectedByOpt = false;
			// ex.printStackTrace();
		}

		return isProtectedByOpt;
	}

	@Deprecated
	public static boolean isXLSProtectionPasswordValid(File xls, String matrixPath, String protPass, Properties p) {
		boolean passwordValid = true;
		try {

			File matrix = new File(matrixPath);
			XLSReader xlsReader = new XLSReader(matrix, p);
			TreeMap<String, Boolean> options = xlsReader.loadProtectionProperties();
			if (options.size() > 0) {
				FileInputStream fis = new FileInputStream(xls);
				POIFSFileSystem s = new POIFSFileSystem(fis);
				HSSFWorkbook workbook = new HSSFWorkbook(s);

				nopasswordSheets.clear();

				Iterator<String> iter = options.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					HSSFSheet sheet = workbook.getSheet(key);
					if (options.get(key) && (!((Short) sheet.getPassword()).toString().equals(protPass))) {
						passwordValid = false;
						nopasswordSheets.add(sheet.getSheetName());
					}
				}
			}

		} catch (Exception ex) {
			passwordValid = false;
		}
		return passwordValid;
	}

	public static boolean isXLSProtectionPasswordValid(XLSReader matXlsReader, XLSReader masterXlsReader) {
		boolean passwordValid = true;
		Map<String, Short> matData = matXlsReader.getAllSheetData();
		Map<String, Short> masterData = masterXlsReader.getAllSheetData();
		Iterator<String> matDataKeyIter = matData.keySet().iterator();
		nopasswordSheets.clear();
		while (matDataKeyIter.hasNext()) {
			String key = matDataKeyIter.next();
			if (key.toLowerCase().trim().equals("options"))
				continue;
			short matVal = matData.get(key);
			if (masterData.get(key) == null)
				break;
			short masterVal = masterData.get(key);
			if ((masterData.get(key) == 1) && (matVal != masterVal))
				nopasswordSheets.add(key);
		}
		if (nopasswordSheets.size() > 0)
			passwordValid = false;
		return passwordValid;
	}
}